package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import persistence.exception.DAOException;
import persistence.util.JdbcUtil;

public class SupplierDAOJdbcImpl extends JdbcDAOBase implements SupplierDAO {
	private CityDAO cityDAO;
	
	public SupplierDAOJdbcImpl() {
		super();
	}
	
	@Override
	public Supplier find(int id) throws DAOException{
		String sql = "SELECT id, name, sap_kreditor, strasse, orte_id FROM lieferanten WHERE id = ?";
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		Supplier supplier = null;
		City city = null;
		
		try {
			con = getConProvider().getConnection();
			prepStmt = con.prepareStatement(sql);
			prepStmt.setInt(1, id);
			rs = prepStmt.executeQuery();
			
			if (rs.next()) {
				city = cityDAO.find(rs.getInt(5));
				supplier = new Supplier (rs.getInt(1), rs.getString(2), rs.getLong(3), rs.getString(4), city);
			}
		}
		catch (SQLException se) {
			throw new DAOException("Problem when attempting to find Supplier with id: " + id, se);
		}
		finally {
			JdbcUtil.cleanup(con, prepStmt, rs);
		}
		return supplier;
	}

	@Override
	public List<Supplier> findAllByTitle(String title) throws DAOException{
		String sql = "SELECT id, name, sap_kreditor, strasse, orte_id FROM lieferanten WHERE name like ?";
		List<Supplier> suppliers = new ArrayList<Supplier>();
		City city = null;
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		
		try {
			con = getConProvider().getConnection();
			prepStmt = con.prepareStatement(sql);
			prepStmt.setString(1, title+"%");
			rs = prepStmt.executeQuery();
			
			while(rs.next()) {
				city = cityDAO.find(rs.getInt(5));
				suppliers.add(new Supplier(rs.getInt(1), rs.getString(2), rs.getLong(3), rs.getString(4), city));
			}
		}
		catch(SQLException se) {
			throw new DAOException("Problem when attempting to find all Suppliers whose title starts with: " + title, se);
		}
		finally {
			JdbcUtil.cleanup(con, prepStmt, rs);
		}
		return suppliers;
	}

	@Override
	public List<Supplier> findAll() throws DAOException {
		return this.findAllByTitle("");
	}	

	@Override
	public void updateSupplier() throws DAOException{
		// TODO Auto-generated method stub

	}

	@Override
	public Supplier create() throws DAOException{
		// TODO Auto-generated method stub
		return null;
	}

	public void setCityDAO(CityDAO cityDAO) {
		this.cityDAO = cityDAO;
	}
}
